SET NOCOUNT ON;
SET XACT_ABORT ON

RAISERROR('!!! Check if Zip_Code is needed ...',0,1);

GO
USE [master];
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'siad_work_olap')
BEGIN
  RAISERROR('Removing exiting OLAPDatabase ...',0,1); --info
	ALTER DATABASE [siad_work_olap] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE [siad_work_olap];
END
GO

RAISERROR('Creating database ''siad_work_olap'' ...',0,1); --info
CREATE DATABASE siad_work_olap;
GO

USE siad_work_olap;
GO

BEGIN TRANSACTION

create table DimLocation
(
	zipCode int primary key,
	stateAbbr varchar(2) not null,
	stateName varchar(20),
	city char(50),
);

create table DimBand
(
	id int primary key identity,
	oltpId int unique not null,
	name varchar(100) not null,
	locationId int references DimLocation(zipCode) not null,
	isActive bit not null 
);

create table DimGenre
(
	id int primary key identity,
	oltpId int unique not null,
	genre varchar(50) not null,
	lastUpdate date
);

create table DimBandGenre
(
	band int references DimBand(oltpId),
	genre int references DimGenre(oltpId),
	primary key(band, genre)
);

create table DimItem
(
	id int primary key identity,
	oltpId int not null,
	name varchar(100) not null,
	typeName varchar(20) not null,
	bandId int references DimBand(oltpId),
	relatedItemId int,
	cost decimal(18,2),
	check (
		(typeName = 'Album' and cost is not null and bandId is not null and relatedItemId is null) or 
		(typeName = 'Song' and cost is null and bandId is not null and relatedItemId is not null) or 
		(typeName = 'Video' and cost is null)
	)
);

create table DimTime
(
	id int primary key identity,
	tHour int,
	tMinute int,
	check (tHour >= 0 and tMinute >= 0),
)

create table DimDate
(
	id int primary key identity,
	dateVal date not null,
	dYear int,
	dQuarter int,
	dMonth int,
	dMonthName varchar(20),
	dWeekDay int,
	dWeekDayName varchar(20),
	dDay int	
);

create table DimAgent (
	id int primary key identity,
	oltpId int unique not null,
	name varchar(100) not null
)

create table FactPerformance (
	id int primary key identity,
	oltpId int not null,
	bandId int references DimBand(oltpId),
	locationId int references DimLocation(zipCode),
	agentId int references DimAgent(oltpId),
	pDate int references DimDate(id),
	revenue decimal(18,2),
	bandRev decimal(18,2),
	bandRevPct decimal(18,2),
	agentRev decimal(18,2),
	agentRevPct decimal(18,2),
	realRevenue decimal(18,2)
);


create table DimDemographics (
	id int primary key identity,
	gender char(1),
	education varchar(100),
	occupation varchar(100),
	incomeClass varchar(50),
	totalChildren int,
	carsOwned int,
	maritalStatus char(1)
)

create table DimCustomer (
	id int unique identity,
	oltpId int not null,
	name varchar(100) not null,
	streetAddress varchar(100),
	phoneNumber varchar(20),
	email varchar(100),
	locationId int references DimLocation(zipCode),
	startDate datetime,
	endDate datetime,
	primary key (oltpId, startDate)
);

create table FactOrder (
	id int primary key identity,
	oltpId int not null,
	orderLine int not null,
	orderTime int references DimTime(id),
	orderDate int references DimDate(id),
	promisedDate int references DimDate(id),
	customerId int references DimCustomer(id),
	demographicsId int references DimDemographics(id),
	bandId int references DimBand(oltpId),
	agentId int references DimAgent(oltpId),
	itemId int references DimItem(id),
	source varchar(5),
	orderTotal decimal(18,2),
	orderSubTotal decimal(18,2),
	orderTax decimal(18,2),
	orderTaxPct decimal(18,2),
	itemQuant int,
	itemPrice decimal(18,2),
	bandRev decimal(18,2),
	bandRevPct decimal(18,2),
	agentRev decimal(18,2),
	agentRevPct decimal(18,2),
	realRevenue decimal(18,2)
);

create table SysSettings (
	lastRun datetime
);
insert into SysSettings values ('01-01-1900')

raiserror('... Ok!',0,1) --info
commit